import json
import os
import sys
import time
import pymysql




class CommonUtils:
    '''
    读取器类:提供读写文件的方法
    '''

    @classmethod
    def read_json(cls,filepath):
        '''
        将指定文件中的json数据转换为python中的字典
        :param filepath:
        :return:
        '''
        # paths = filepath.split('/')
        # filepath = os.path.join(sys.path[1], paths[0])
        # filepath = os.path.join(filepath, paths[1])
        #
        filepath = cls.get_abs_path(filepath)
        data = None
        with open(filepath,encoding='utf8') as file:
            import json5
            data = json5.load(file)
        return data

    @classmethod
    def read_json2(cls, filepath,start, end):
        filepath = cls.get_abs_path(filepath)
        data = None
        with open(filepath, encoding='utf8') as file:
            data = file.readlines()

        data = data[1:len(data) - 1]

        for index in range(len(data)):
            if ',\n' in data[index]:
                data[index] = data[index].replace(',\n', '')
            if '\n' in data[index]:
                data[index] = data[index].replace('\n', '')
            data[index] = eval(data[index])

        return data[start:end]

    @classmethod
    def read_excel(cls, section, option):
        '''
        返回指定页面的指定功能的测试数据,返回数据类型为[{key:value}]
        :param section:
        :param option:
        :return:
        '''
        import xlrd
        data = cls.read_ini_by_option('config/testdata.ini',section, option)
        abs_path = CommonUtils.get_abs_path(data['path'])
        workbook = xlrd.open_workbook(abs_path)
        sheet = workbook.sheet_by_name(data['sheetname'])
        version_sheet = workbook.sheet_by_name('version')
        version_no = version_sheet.cell(1,0).value
        module_name = version_sheet.cell(1,1).value
        list_temp = []
        for row in range(data['start_row'],data['end_row']):
            list_row = []
            for col in range(data['start_col'],data['end_col']):
                cell = sheet.cell(row, col).value
                list_row.append(cell)

            strs_params = list_row[6].split('.')

            # str_param = ""
            str_param = strs_params[1]+"."+strs_params[2]+"."+strs_params[3]+"."+strs_params[4]
            # for item in range(1,len(strs_params)):
            #     str_param += strs_params[item]
            str_param = str_param.replace('“', '"').strip()
            str_param = str_param.replace('。','.')
            print(f'row:{row},str_param:{str_param}')

            list_row[6] = eval(str_param)
            strs_expect = list_row[7].split('.')
            list_row[7] = strs_expect[1].strip()
            # params = ''
            # for item in strs:
            #     params = params + item
            # list_row[1] = eval(params)

            dict_row = {"case_no":list_row[0],"params":list_row[6],"expected":list_row[7],
                        "test_type":list_row[11], "test_steps":"", "version":version_no, "module":module_name}

            list_temp.append(dict_row)
        return list_temp

    @classmethod
    def read_ini_by_option(cls, path, section, option):
        '''
        从cnf文件中读取option数据
        :param path:
        :param section:
        :param option:
        :return:
        '''
        dict_section = cls.read_ini_by_section(path, section)
        res = dict_section[option]
        return res


    @classmethod
    def read_ini_by_section(cls, path, section):
        '''
        从cnf文件中读取section数据
        :param path:
        :param section:
        :return:
        '''
        # 获取绝对路径
        path = CommonUtils.get_abs_path(path)
        with open(path, encoding='utf8') as file:
            contents = file.read()
        contents = contents.split('\n')

        dict_temp = {}
        length = len(contents) #获取列表长度

        for index in range(length): #遍历列表
            row = cls.get_valid_data(contents[index]) #提取数据
            if (row is not None) and isinstance(row, str) and (row == section):#如果是有效数据并且是section并且是指定的section
                # 从下一行开始添加option,直到下一个section或列表末尾为止
                index += 1
                while index<length and (not (isinstance(cls.get_valid_data(contents[index]),str))):
                    row_temp = cls.get_valid_data(contents[index])
                    if isinstance(row_temp, dict):
                        dict_temp[row_temp['key']] = row_temp['value']
                    index += 1

        return dict_temp

    @classmethod
    def get_valid_data(cls, row):
        '''
        从row中提取出有效数据,如果没有有效数据,则返回None,
        否则返回有效的数据:
        如果是option,则返回字典,格式为{"key":"add_assets","value":{"path":"data/admint_data.xls","sheetname":"purreg","start_row":1,"end_row":4,"params_col":2,"expected_col":3}}
        如果是section，则返回类型为字符串
        :param row:
        :return:
        '''
        if row == "":
            return None

        result = None
        index = -1

        try:
            index = row.index('# ')
        except Exception:
            index = -1

        if index > -1: # 存在'# '
            row = row[:index]
            if '[' in row:
                row = row.strip()
                row = row[1:(len(row) - 1)]
                result = row
            else:
                if '=' in row:
                    strs = row.split('=')
                    key = strs[0].strip()
                    value = eval(strs[1].strip())
                    result = {"key": key, "value": value}
                else:
                    result = None

        else:# 没有'# '
            if '=' in row:
                strs = row.split('=')
                key = strs[0].strip()
                value = eval(strs[1].strip())
                result = {"key":key,"value":value}
            else:
                row = row.strip()
                row = row[1:(len(row)-1)]
                result = row

        return result

    @classmethod
    def get_abs_path(cls,filepath):
        '''
        获取绝对路径(参数路径要求为:"config/login.config"的两层,返回为ProjectPath/config/login.ini的绝对路径)
        :param filepath:
        :return:
        '''
        paths = filepath.split('/')
        project_path = CommonUtils.get_project_path()
        filepath = os.path.join(project_path, paths[0])
        filepath = os.path.join(filepath, paths[1])
        return filepath

    @classmethod
    def get_abs_path_single(cls,filepath):
        '''
        获取绝对路径(输入路径要求为:"login.config"的单层,返回为ProjectPath/login.ini的绝对路径)
        :param filepath:
        :return:
        '''
        project_path = CommonUtils.get_project_path()
        return os.path.join(project_path, filepath)


    @classmethod
    def assure_exist(cls, file_path):
        """
        如果指定的目录或文件不存在,则创建这个目录或文件
        :param file_path:
        :return:
        """
        if not os.path.exists(file_path):
            os.mkdir(file_path)

    @classmethod
    def get_project_path(cls):
        '''
        获取项目的绝对路径
        :return:
        '''
        return os.path.dirname(os.path.dirname(__file__))


class DBConnection:
    '''
    提供数据库相关的操作
    '''
    conn = None
    cursor = None

    @classmethod
    def build_conn(cls, option):
        '''
        建立数据库连接,创建并返回游标
        :return:
        '''
        data = CommonUtils.read_ini_by_option('config/db.ini', 'db', option)
        cls.conn = pymysql.Connect(host=data['host'], port=data['port'], user=data['user'], password=data['password'], database=data['database'])
        cls.cursor = cls.conn.cursor()
        return cls.cursor

    @classmethod
    def close_conn(cls):
        '''
        关闭数据库的游标、连接
        :return:
        '''
        cls.cursor.close()
        cls.conn.close()

    @classmethod
    def query(cls, sql, option='app_db'):
        '''
        执行查询SQL语句,返回查询结果
        :param sql:
        :return:
        '''
        cls.build_conn(option)
        # 数据库查询记录
        cls.cursor.execute(sql)
        res = cls.cursor.fetchall()
        cls.close_conn()
        return res

    @classmethod
    def dml(cls, sql, option='app_db'):
        '''
        执行增、删、改SQL操作
        :param sql:
        :return:
        '''
        try:
            cls.build_conn(option)
            res = cls.cursor.execute(sql)
        except Exception as e:
            cls.conn.rollback()
            cls.close_conn()
        else:
            cls.conn.commit()
            cls.close_conn()

class Time:
    @classmethod
    def get_format_time(cls, format_str="%Y-%m-%d"):
        '''返回格式化的时间字符串'''
        return time.strftime(format_str,time.localtime())

class LogHandler:
    @classmethod
    def record(cls, postion, msg):
        '''
        将出错的位置(模块、类、方法)和错误信息保存在logs/record.log
        :param postion:
        :param msg:
        :return:
        '''
        # 获取项目woniuboss的绝对路径,再拼接logs路径
        logs_path = os.path.join(CommonUtils.get_project_path(),"logs")
        # 确保logs目录存在
        CommonUtils.assure_exist(logs_path)
        # 日志文件名
        log_name = Time.get_format_time() + ".log"
        # 日志的绝对路径
        log_path = os.path.join(logs_path, log_name)
        with open(log_path, mode='a', encoding='utf-8') as file:
            form_time = Time.get_format_time("%Y-%m-%d %H:%M:%S")
            # 将内容拼接之后写入日志文件
            file.write(form_time + "\t" + postion + "\t" +msg + "\n")

class CommonTools:

    """
    该类中的方法都是对python基础功能的简单封装
    """

    @classmethod
    def sub_str(cls, str1, str2):  # 会实现
        """
        两个字符串相减，返回字符串的差
        :param str1: 较长的字符串
        :param str2: 较短的字符串
        :return: 字符串的差
        """
        return str1[len(str2):]

    @classmethod
    def create_path(cls, path):   # 会实现
        """
        如果传入的路径不存在就创建它，如果存在就什么也不做
        :param path: 要检查的路径
        :return: None
        """
        if not os.path.exists(path):
            os.mkdir(path)

    @classmethod
    def get_app_path(cls):    # 会实现
        """
        获得应用程序的根路径
        :return:路径字符串
        """
        abspath = sys.path[0]
        rootpath = sys.path[1]
        app_name = cls.sub_str(abspath, rootpath)[1:].split('\\')[0]
        return rootpath + "\\" + app_name

    @classmethod
    def get_ltime(cls, form):    # 会实现
        """
        按格式要求返回当前的日期和时间
        :param form: 日期时间格式
        :return: 日期时间字符串
        """
        return time.strftime(form, time.localtime())

    @classmethod
    def assert_equal(cls, expected, actual):
        if expected == actual:
            print('测试通过')
        else:
            print('测试失败')



if __name__ == '__main__':
    # data = Reader.read_ini_by_option('config/testdata.config', 'purreg', 'add_assets')
    # data = Reader.read_ini_by_section('locator/locator.config', 'add_assets')
    # print(data)
    # conn = DBConnection.build_conn()
    # print(conn)
    # DBConnection.close_conn()
    # print(sys.path)
    # for i in range(3):
    #     LogHandler.record("src.entity.Goods.getId", "error:test!!!")
    # print(Reader.get_abs_path_single('config'))
    # res1 = Reader.read_ini_by_option("config/testdata.config", "purreg", "add_assets")
    # print(res1)
    # print(type(res1))
    # res2 = Reader.read_ini_by_option("config/pages_url.config", "emp_manage_test.emp_info", "empinfo")
    # print(res2)
    # print(type(res2))
    # res3 = Reader.read_ini_by_section("config/testdata.config", "resource_manager")
    # print(res3)
    # print(type(res3))

    # module_obj = __import__(module_full_name, fromlist=[mname])  # 返回模块对象
    # import inspect
    #
    # class_names = inspect.getmembers(module_obj, inspect.isclass)
    # print(os.path.basename("abc/def/ghi.py"))
    # Tester.execute_testcases("testcases")
    # pass
    # module_obj = __import__("testcases.admin_int_test.purchase_register_ui_test", fromlist=["purchase_register_ui_test"])
    # print(module_obj)
    # import inspect
    # class_names = inspect.getmembers(module_obj, inspect.isclass)
    # print(class_names)
    # for item in class_names:
    #     if item[0].endswith("Test"):
    #         print(item)
    # ls = CommonUtils.read_excel("purreg","add_assets")
    # for item in ls:
    #     print(item)

    # data = CommonUtils.read_excel('ui.emp_manage', 'emp_info')
    # print(data)
    # TestCase().assert_equal(data, '添加成功')
    # data = CommonUtils.read_json2('data/data.json',start=0,end=2)
    # data = data[1:len(data)-1]
    # print(data)
    # print('*'*15)
    # for index in range(len(data)):
    #     if ',\n' in data[index]:
    #         data[index] = data[index].replace(',\n','')
    #     if '\n' in data[index]:
    #         data[index] = data[index].replace('\n', '')
    #     data[index] = eval(data[index])
    #
    # # for iten in data:
    # #     print(iten)
    # print(data)
    data = CommonUtils.read_excel(section="look_order",option="parkingid")
    print(data)